CREATE EXTERNAL TABLE jms_tmp.tmp_out_port_shiipment_billcode_detail_hi(
  billcode string COMMENT '单号',
  shipment_no string COMMENT '任务号',
  line_name string COMMENT '线路名称',
  send_code string COMMENT '发件站点code',
  send_name string COMMENT '发件站点name',
  arrival_code string COMMENT '到件站点code',
  arrival_name string COMMENT '到件站点name',
  vehicle_typegroup string COMMENT '实际车型',
  shipment_business_attribute string COMMENT '业务属性（正班加班）',
  planned_departure_time timestamp COMMENT '规划发车时间',
  load_scan_time timestamp COMMENT '发车站点扫描时间',
  planned_arrival_time timestamp COMMENT '规划到车时间',
  actual_departure_time timestamp COMMENT '实际发车时间',
  is_trunk_branch int COMMENT '支干线类型(1支线2干线)',
  send_network_type int COMMENT '发件网点类型',
  package_inside_charge_weight decimal(16,2) COMMENT '内部计费重量',
  send_agent_code string COMMENT '发车网点所属代理区code',
  send_agent_name string COMMENT '发车网点所属代理区name',
  send_regional_id string COMMENT '发车网点所属大区id',
  send_regional_desc string COMMENT '发车网点所属大区name',
  send_provider_id string COMMENT '发车网点所属代理区id',
  send_provider_desc string COMMENT '发车网点所属代理区name',
  is_can_zgx string COMMENT '判断支干线名称',
  plan_shift_code string COMMENT '规划任务编码',
  plan_shift_name string COMMENT '规划任务名称',
  pre_line_type string COMMENT '发件站点上一站支干线类型',
  pre_network_type string COMMENT '发件站点上一站类型',
  vehicleweight decimal(16,2) COMMENT '核载重量',
  vehiclecount int COMMENT '核载票量',
  data_type string COMMENT '出港类型 中转、同城、出港')
COMMENT '进出港预测预警_出港实际任务号单号明细'
PARTITIONED BY (dt string COMMENT '时间分区 规划发车日期')
STORED AS PARQUET
LOCATION '/dw/hive/jms_tmp.db/external/tmp_out_port_shiipment_billcode_detail_hi'
TBLPROPERTIES (
    'discover.partitions' = 'false',
    'parquet.column.index.access' = 'true'
);
--预测预警出港单号明细
CREATE EXTERNAL TABLE jms_tmp.tmp_out_port_forecast_warning_billcode_detail_hi(
  waybill_no string COMMENT '单号',
  package_inside_charge_weight decimal(10,2) COMMENT '内部计费重量',
  taking_scan_time string COMMENT '揽收时间',
  in_branch_id string COMMENT '集货路由id',
  main_id string COMMENT '干线路由id',
  out_branch_id string COMMENT '散货路由id',
  in_edge_latest_warehousing timestamp COMMENT '最晚入仓时间',
  pick_network_code string COMMENT '取件网点',
  send_code string COMMENT '发车网点编码',
  send_name string COMMENT '发车网点名称',
  send_network_type int COMMENT '网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点',
  line_name string COMMENT '线路名称',
  planned_departure_time timestamp COMMENT '规划发车时间',
  arrive_code string COMMENT '到车网点编码',
  arrive_name string COMMENT '到车网点名称',
  dispatch_network_code string COMMENT '派件网点编码',
  sort_num int COMMENT '当前站点排序，不是连续值',
  vehicle_typegroup string COMMENT '车型',
  send_agent_code string COMMENT '发出网点所属代理区编码',
  send_agent_name string COMMENT '发出网点所属代理区名称',
  send_regional_id string COMMENT '发出网点所属大区id',
  send_regional_desc string COMMENT '发出网点所属大区',
  send_provider_id string COMMENT '发出网点所属省份id',
  send_provider_desc string COMMENT '发出网点所属省份',
  shipment_no string COMMENT '任务号',
  shipment_actual_departure_time timestamp COMMENT '任务号实际发车时间',
  shipment_business_attribute string COMMENT '任务号类型',
  vehicleweight int COMMENT '核载重量',
  vehiclecount int COMMENT '核载票量',
  data_type string COMMENT '类型类型 出港、同城、中转',
  update_time timestamp COMMENT '更新时间',
  shipment_shifts string COMMENT '实际班次',
  forecast_shifts string COMMENT '预测班次',
  is_trunk_branch int COMMENT '支干线：1-干线；2-支线',
  forecast_shift_code string COMMENT '预测班次编码',
  forecast_shift_name string COMMENT '预测班次名称',
  planned_arrival_time timestamp COMMENT '规划到达时间')
COMMENT '进出港预测预警_出港单号明细'
PARTITIONED BY ( dt string COMMENT '时间分区 揽收时间')
STORED AS PARQUET
LOCATION '/dw/hive/jms_tmp.db/external/tmp_out_port_forecast_warning_billcode_detail_hi'
TBLPROPERTIES (
    'discover.partitions' = 'false',
    'parquet.column.index.access' = 'true'
);
----线路明细
alter table jms_dm.dm_out_port_forecast_warning_line_detail_dt set tblproperties ('external.table.purge'='true');
drop table jms_dm.dm_out_port_forecast_warning_line_detail_dt;
CREATE EXTERNAL TABLE jms_dm.dm_out_port_forecast_warning_line_detail_dt(
  planned_departure_date date COMMENT '规划发车日期',
  send_regional_id string COMMENT '发车网点所属大区id',
  send_regional_desc string COMMENT '发车网点所属大区',
  send_provider_id string COMMENT '发车网点所属省id',
  send_provider_desc string COMMENT '发车网点所属省',
  send_agent_code string COMMENT '发车网点所属代理区编码',
  send_agent_name string COMMENT '发车网点所属代理区名称',
  send_code string COMMENT '发车网点编码',
  send_name string COMMENT '发车网点名称',
  send_network_type int COMMENT '发车网点类型 4中心，5集散点，6网点',
  arrive_code string COMMENT '到车网点编码',
  arrive_name string COMMENT '到车网点名称',
  data_type string COMMENT '类型：出港 同城 中转',
  line_name string COMMENT '线路名称',
  vehicle_typegroup string COMMENT '车型',
  shipment_no string COMMENT '任务号',
  shipment_business_attribute string COMMENT '任务属性',
  planned_departure_time timestamp COMMENT '规划发车时间',
  vehiclecount int COMMENT '核载票数',
  vehicleweight decimal(16,2) COMMENT '核载重量',
  forecast_waybill_cnt int COMMENT '预测发出票数',
  forecast_waybill_weight decimal(16,2) COMMENT '预测发出重量',
  forecast_load_cnt int COMMENT '预估装载率-票量',
  forecast_load_weight decimal(16,2) COMMENT '预估装载率-重量',
  is_have_risk int COMMENT '是否有风险',
  forecast_recommend string COMMENT '预测推荐 加班车、推荐路由',
  shipment_actual_departure_time timestamp COMMENT '任务号实际发车时间',
  shipment_loading_waybill_cnt int COMMENT '线路实际装载-票数',
  shipment_loading_waybill_weight decimal(16,2) COMMENT '线路实际装载重量',
  actual_loading_waybill_cnt int COMMENT '单号匹配票量',
  actual_loading_waybill_weight decimal(16,2) COMMENT '单号匹配重量',
  forecast_shift_code string COMMENT '规划班次编码',
  forecast_shift_name string COMMENT '规划班次名称',
  is_trunk_branch int COMMENT '支线干线1干线2支线')
COMMENT '进出港预测预警_出港线路明细'
PARTITIONED BY (
  dt string COMMENT '规划发车时间')
STORED AS PARQUET
LOCATION '/dw/hive/jms_dm.db/external/dm_out_port_forecast_warning_line_detail_dt'
TBLPROPERTIES (
    'discover.partitions' = 'false',
    'parquet.column.index.access' = 'true'
);

---汇总
alter table jms_dm.dm_in_out_port_forecast_warning_summary_dt set tblproperties ('external.table.purge'='true');
drop table jms_dm.dm_in_out_port_forecast_warning_summary_dt;
CREATE EXTERNAL TABLE jms_dm.dm_in_out_port_forecast_warning_summary_dt(
  data_date timestamp COMMENT '日期 出港规划发车日期 进港规划到车日期',
  regional_id string COMMENT '大区',
  regional_desc string COMMENT '大区',
  provider_id string COMMENT '省份',
  provider_desc string COMMENT '省份',
  agent_code string COMMENT '代理区编码',
  agent_name string COMMENT '代理区名称',
  network_code string COMMENT '网点编码( 进港到车|出港发车网点）',
  network_name string COMMENT '网点名称',
  network_type int COMMENT '网点类型网点类型 4-中心,5-集散点,6-网点',
  shifts string COMMENT '班次',
  forecast_waybill_cnt_out int COMMENT '发货预测-出港-票量',
  forecast_waybill_cnt_samecity int COMMENT '发货预测-同城-票量',
  forecast_waybill_cnt_transfer int COMMENT '发货预测-中转-票量',
  forecast_waybill_cnt_other int COMMENT '发货预测-其他-票量',
  forecast_waybill_cnt int COMMENT '发货预测-合计',
  forecast_vehicle_cnt_branch int COMMENT '预计发出车辆数-支线',
  forecast_vehicle_cnt_main int COMMENT '预计发出车辆数-干线',
  forecast_vehicle_cnt int COMMENT '预计发出车辆数-合计',
  shipment_loading_waybill_cnt_out int COMMENT '实际发货-出港-票量',
  shipment_loading_waybill_cnt_samecity int COMMENT '实际发货-同城-票量',
  shipment_loading_waybill_cnt_transfer int COMMENT '实际发货-中转-票量',
  shipment_loading_waybill_cnt_other int COMMENT '实际发货-其他-票量',
  shipment_loading_waybill_cnt int COMMENT '实际发货-合计',
  shipment_vehicle_cnt_branch int COMMENT '实际发车车辆数-支线',
  shipment_vehicle_cnt_main int COMMENT '实际发车车辆数-干线',
  shipment_vehicle_cnt int COMMENT '发货预测车辆数-出港',
  actual_bill_cnt int COMMENT '单号匹配量',
  data_type string COMMENT '报表数据类型 进港 出港',
  is_trunk_branch int COMMENT '支线干线1干线2支线')
COMMENT '进出港预测预警_网点汇总'
PARTITIONED BY (
  dt string COMMENT '规划发车时间',
  datatype string COMMENT '报表类型：进港in 出港out')
LOCATION '/dw/hive/jms_dm.db/external/dm_in_out_port_forecast_warning_summary_dt'
TBLPROPERTIES (
    'discover.partitions' = 'false',
    'parquet.column.index.access' = 'true'
);